from sqlalchemy import func, and_, select, extract, union_all
from datetime import datetime
from module_admin.entity.do.employee_do import EmployeeInfo
from module_admin.entity.do.attendance_do import AttendanceInfo
from module_admin.entity.do.employee_salary_do import EmployeeSalary
from module_admin.entity.do.employee_social_security_do import EmployeeSocialSecurity
from module_admin.entity.do.employee_payment_do import EmployeePayment
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.sql import text

class SalaryService:
    def __init__(self, db: AsyncSession):
        self.db = db

    async def get_salary_list(self, query_params):
        """获取薪资列表"""

        # Subquery for aggregated attendance data
        attendance_subquery = (
            select(
                AttendanceInfo.employee_id,
                func.extract('month', AttendanceInfo.attendance_date).label('month'),
                func.extract('year', AttendanceInfo.attendance_date).label('year'),
                func.sum(AttendanceInfo.hours_worked / 8).label('total_attendance_days')
            )
            .where(AttendanceInfo.del_flag == '0')
            .group_by(
                AttendanceInfo.employee_id,
                func.extract('month', AttendanceInfo.attendance_date),
                func.extract('year', AttendanceInfo.attendance_date)
            )
        ).subquery('attendance_data')

        # Subquery for aggregated payment data
        payment_subquery = (
            select(
                EmployeePayment.employee_id,
                func.extract('month', EmployeePayment.payment_date).label('month'),
                func.extract('year', EmployeePayment.payment_date).label('year'),
                func.sum(EmployeePayment.payment_amount).label('total_payment_amount')
            )
            .where(EmployeePayment.del_flag == '0')
            .group_by(
                EmployeePayment.employee_id,
                func.extract('month', EmployeePayment.payment_date),
                func.extract('year', EmployeePayment.payment_date)
            )
        ).subquery('payment_data')

        # Subquery for social security amount per employee (assuming one active record per employee)
        # If there can be multiple, we need a strategy to pick one (e.g., latest by a timestamp)
        social_security_subquery = (
            select(
                EmployeeSocialSecurity.employee_id,
                func.max(EmployeeSocialSecurity.social_security_amount).label('max_social_security_amount') # Use max to handle potential duplicates for same employee_id, if they exist
            )
            .where(EmployeeSocialSecurity.del_flag == '0')
            .group_by(EmployeeSocialSecurity.employee_id)
        ).subquery('social_security_data')


        # Union of distinct employee-month-year combinations from attendance and payment
        # This creates a comprehensive list of all months an employee has any activity
        all_months_cte = (
            select(attendance_subquery.c.employee_id, attendance_subquery.c.month, attendance_subquery.c.year)
            .union(
                select(payment_subquery.c.employee_id, payment_subquery.c.month, payment_subquery.c.year)
            )
        ).cte('all_months')


        # Main query
        query = (
            select(
                EmployeeInfo.id.label('employeeId'),
                EmployeeInfo.name.label('employeeName'),
                EmployeeSalary.current_year_daily_wage.label('dailySalary'),
                all_months_cte.c.month.label('month'),
                all_months_cte.c.year.label('year'),
                func.coalesce(attendance_subquery.c.total_attendance_days, 0).label('attendanceDays'),
                (EmployeeSalary.current_year_daily_wage * func.coalesce(attendance_subquery.c.total_attendance_days, 0)).label('grossSalary'),
                func.coalesce(payment_subquery.c.total_payment_amount, 0).label('totalPayment'),
                func.coalesce(social_security_subquery.c.max_social_security_amount, 0).label('socialSecurity'), # Use the aggregated social security amount
                ((EmployeeSalary.current_year_daily_wage * func.coalesce(attendance_subquery.c.total_attendance_days, 0)) -
                 func.coalesce(payment_subquery.c.total_payment_amount, 0) -
                 func.coalesce(social_security_subquery.c.max_social_security_amount, 0)).label('netSalary') # Use the aggregated social security amount
            )
            .join(EmployeeSalary, EmployeeInfo.id == EmployeeSalary.employee_id)
            .outerjoin(social_security_subquery, EmployeeInfo.id == social_security_subquery.c.employee_id) # Join with the social security subquery
            .join(all_months_cte, EmployeeInfo.id == all_months_cte.c.employee_id) # Join with the comprehensive month list
            .outerjoin(attendance_subquery, and_(
                EmployeeInfo.id == attendance_subquery.c.employee_id,
                all_months_cte.c.month == attendance_subquery.c.month,
                all_months_cte.c.year == attendance_subquery.c.year
            ))
            .outerjoin(payment_subquery, and_(
                EmployeeInfo.id == payment_subquery.c.employee_id,
                all_months_cte.c.month == payment_subquery.c.month,
                all_months_cte.c.year == payment_subquery.c.year
            ))
            .where(
                EmployeeInfo.del_flag == '0',
                EmployeeSalary.del_flag == '0'
            )
            .group_by(
                EmployeeInfo.id,
                EmployeeInfo.name,
                EmployeeSalary.current_year_daily_wage,
                all_months_cte.c.month,
                all_months_cte.c.year,
                social_security_subquery.c.max_social_security_amount, # Group by the aggregated social security value
                attendance_subquery.c.total_attendance_days,
                payment_subquery.c.total_payment_amount
            )
            .order_by(EmployeeInfo.id, all_months_cte.c.year, all_months_cte.c.month)
        )

        # Add query conditions
        if query_params.get('name'):
            query = query.where(EmployeeInfo.name.like(f"%{query_params['name']}%"))
        if query_params.get('month'):
            query = query.where(all_months_cte.c.month == query_params['month'])

        # Calculate total
        count_query = select(func.count()).select_from(query.subquery())
        total = await self.db.scalar(count_query)

        # Pagination
        page_num = query_params.get('pageNum', 1)
        page_size = query_params.get('pageSize', 10)
        query = query.offset((page_num - 1) * page_size).limit(page_size)

        # Get results
        result = await self.db.execute(query)
        salary_list = result.all()

        # Convert to dict list
        salary_dict_list = []
        for salary in salary_list:
            salary_dict = {
                'employeeId': salary.employeeId,
                'employeeName': salary.employeeName,
                'month': salary.month,
                'year': salary.year,
                'dailySalary': float(salary.dailySalary),
                'attendanceDays': float(salary.attendanceDays),
                'grossSalary': float(salary.grossSalary),
                'totalPayment': float(salary.totalPayment) if salary.totalPayment else 0.0,
                'socialSecurity': float(salary.socialSecurity),
                'netSalary': float(salary.netSalary)
            }
            salary_dict_list.append(salary_dict)

        return {
            'rows': salary_dict_list,
            'total': total
        }

    async def get_salary_by_id(self, salary_id):
        """根据ID获取薪资信息"""
        # 由于没有sys_salary表，这个方法需要重新实现
        # 暂时返回None
        return None

    async def create_salary(self, salary_data):
        """创建薪资记录"""
        # 由于没有sys_salary表，这个方法需要重新实现
        # 暂时抛出异常
        raise ValueError("薪资记录创建功能暂未实现")

    async def update_salary(self, salary_data):
        """更新薪资记录"""
        # 由于没有sys_salary表，这个方法需要重新实现
        # 暂时抛出异常
        raise ValueError("薪资记录更新功能暂未实现")

    async def delete_salary(self, salary_id):
        """删除薪资记录"""
        # 由于没有sys_salary表，这个方法需要重新实现
        # 暂时返回False
        return False 